-- retrieve deadlock information from the system_health session
IF LEFT(CONVERT(CHAR(2), SERVERPROPERTY('ProductVersion')), 2) >= '11'
	-- >= SQL Server 2012
	SELECT
		XEvent.query('data[@name="xml_report"]/value/deadlock') AS deadlock_graph
	FROM (SELECT CAST(target_data AS XML) AS TargetData
			FROM sys.dm_xe_session_targets st
			INNER JOIN sys.dm_xe_sessions s 
				ON s.address = st.event_session_address
			WHERE s.name = 'system_health'
				AND st.target_name = 'ring_buffer') AS Data
		  CROSS APPLY TargetData.nodes ('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData (XEvent);
ELSE
	-- SQL Server 2008
	SELECT CAST(event_data.value('(event/data/value)[1]', 'varchar(max)') AS XML) AS deadlock_graph
	FROM
	( SELECT XEvent.query('.') AS event_data
		FROM (SELECT CAST(target_data AS XML) AS TargetData
			FROM sys.dm_xe_session_targets st
				JOIN sys.dm_xe_sessions s
					ON s.address = st.event_session_address
			WHERE name = 'system_health'
				AND target_name = 'ring_buffer'
		) AS Data
		CROSS APPLY TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]')
		AS XEventData ( XEvent )
	) AS tab ( event_data );

-- waitresource KEY: 8:72057594064666624 (dc9ad24a8317)
-- sql_handle 0x030008006df54d6d20cfe4008aa1000001000000000000000000000000000000000000000000000000000000

-- move to that database
SELECT DB_NAME(<db_id>) AS database_name;

-- take that result and
USE <db_name>;

-- find the associated objects
SELECT
    OBJECT_NAME(p.object_id) AS table_name, 
    i.name AS index_name
FROM sys.partitions AS p 
     INNER JOIN sys.indexes AS i ON p.object_id = i.object_id 
		AND p.index_id = i.index_id 
WHERE partition_id = <associatedObjectId>;

-- now find out what these hobt_ids represent
SELECT
	OBJECT_SCHEMA_NAME(object_id) AS 'schema_name',
	OBJECT_NAME(object_id) AS 'object_name'
FROM sys.partitions 
WHERE hobt_id IN (<hobt_ids>);

-- and the batches involved (if they're still around) using
SELECT * FROM sys.dm_exec_sql_text(<sql_handle>);